package MySQL::SNMP;

=head1 NAME

MySQL::SNMP - Gather MySQL statistics to expose via SNMP

=head1 SYNOPSIS

use MySQL::SNMP qw(fetch_mysql_data mysql_oidvals);

# fetch the list of supported statistics and oid number mappings -
# items in this list are anonymous hashes with the structure:
# { oid => int, type => string, new_key => string, old_key => string }
# where type is the SNMP object format (Counter32, Counter64, Gauge32),
# oid corresponds to the mib object number, and new_key to the object name
# see MYSQL-SERVER-MIB.txt for details
my @oidvals = @{ mysql_oidvals() };

# gather mysql statistics - returns a hashref
my $cur_status = fetch_mysql_data($dsn, $username, $password, {master => 1, innodb => 1, procs => 1,});

# return statistics key from the C<new_key> in @oidvals with an embedded hash
# for the associated object C<type> and C<value> parameters when available,
# or C<undef> when the statistic was not collected

# C<value> may be a plain integer, or a C<Math::BigInt> object, use the
# C<bstr()> method to convert to a plain string before returning if necessary

=cut

use strict;
use warnings;

require Exporter;
our @ISA = qw(Exporter);
our @EXPORT_OK = qw(fetch_mysql_data mysql_oidvals);


our $VERSION = "v1.0_02b1";

require MySQL::SNMP::InnoDBParser;
use Unix::Syslog qw(:subs :macros);
use DBI;
use DBD::mysql;

my $innodb_parser = new MySQL::SNMP::InnoDBParser;

my %opt = (
    master     => 1,
    slave      => 0,
    slavebytes => 0,
    innodb     => 1,
    procs      => 0,
    host       => 'localhost',
    heartbeat  => ''
);

my @oidvals = (
    {oid => 1, type => 'Counter32', new_key => 'myKeyReadRequests', old_key => 'Key_read_requests',},
    {oid => 2, type => 'Counter32', new_key => 'myKeyReads', old_key => 'Key_reads',},
    {oid => 3, type => 'Counter32', new_key => 'myKeyWriteRequests', old_key => 'Key_write_requests',},
    {oid => 4, type => 'Counter32', new_key => 'myKeyWrites', old_key => 'Key_writes',},
    {oid => 5, type => 'Gauge32', new_key => 'myHistoryList', old_key => 'history_list',},
    {oid => 6, type => 'Counter64', new_key => 'myInnodbTransactions', old_key => 'innodb_transactions',},
    {oid => 7, type => 'Gauge32', new_key => 'myReadViews', old_key => 'read_views',},
    {oid => 8, type => 'Gauge32', new_key => 'myCurrentTransactions', old_key => 'current_transactions',},
    {oid => 9, type => 'Gauge32', new_key => 'myLockedTransactions', old_key => 'locked_transactions',},
    {oid => 10, type => 'Gauge32', new_key => 'myActiveTransactions', old_key => 'active_transactions',},
    {oid => 11, type => 'Gauge32', new_key => 'myPoolSize', old_key => 'pool_size',},
    {oid => 12, type => 'Gauge32', new_key => 'myFreePages', old_key => 'free_pages',},
    {oid => 13, type => 'Gauge32', new_key => 'myDatabasePages', old_key => 'database_pages',},
    {oid => 14, type => 'Gauge32', new_key => 'myModifiedPages', old_key => 'modified_pages',},
    {oid => 15, type => 'Counter32', new_key => 'myPagesRead', old_key => 'pages_read',},
    {oid => 16, type => 'Counter32', new_key => 'myPagesCreated', old_key => 'pages_created',},
    {oid => 17, type => 'Counter32', new_key => 'myPagesWritten', old_key => 'pages_written',},
    {oid => 18, type => 'Counter32', new_key => 'myFileFsyncs', old_key => 'file_fsyncs',},
    {oid => 19, type => 'Counter32', new_key => 'myFileReads', old_key => 'file_reads',},
    {oid => 20, type => 'Counter32', new_key => 'myFileWrites', old_key => 'file_writes',},
    {oid => 21, type => 'Counter32', new_key => 'myLogWrites', old_key => 'log_writes',},
    {oid => 22, type => 'Gauge32', new_key => 'myPendingAIOLogIOs', old_key => 'pending_aio_log_ios',},
    {oid => 23, type => 'Gauge32', new_key => 'myPendingAIOSyncIOs', old_key => 'pending_aio_sync_ios',},
    {oid => 24, type => 'Gauge32', new_key => 'myPendingBufPoolFlushes', old_key => 'pending_buf_pool_flushes',},
    {oid => 25, type => 'Gauge32', new_key => 'myPendingChkpWrites', old_key => 'pending_chkp_writes',},
    {oid => 26, type => 'Gauge32', new_key => 'myPendingIbufAIOReads', old_key => 'pending_ibuf_aio_reads',},
    {oid => 27, type => 'Gauge32', new_key => 'myPendingLogFlushes', old_key => 'pending_log_flushes',},
    {oid => 28, type => 'Gauge32', new_key => 'myPendingLogWrites', old_key => 'pending_log_writes',},
    {oid => 29, type => 'Gauge32', new_key => 'myPendingNormalAIOReads', old_key => 'pending_normal_aio_reads',},
    {oid => 30, type => 'Gauge32', new_key => 'myPendingNormalAIOWrites', old_key => 'pending_normal_aio_writes',},
    {oid => 31, type => 'Counter32', new_key => 'myIbufInserts', old_key => 'ibuf_inserts',},
    {oid => 32, type => 'Counter32', new_key => 'myIbufMerged', old_key => 'ibuf_merged',},
    {oid => 33, type => 'Counter32', new_key => 'myIbufMerges', old_key => 'ibuf_merges',},
    {oid => 34, type => 'Counter32', new_key => 'mySpinWaits', old_key => 'spin_waits',},
    {oid => 35, type => 'Counter32', new_key => 'mySpinRounds', old_key => 'spin_rounds',},
    {oid => 36, type => 'Counter32', new_key => 'myOsWaits', old_key => 'os_waits',},
    {oid => 37, type => 'Counter32', new_key => 'myRowsInserted', old_key => 'rows_inserted',},
    {oid => 38, type => 'Counter32', new_key => 'myRowsUpdated', old_key => 'rows_updated',},
    {oid => 39, type => 'Counter32', new_key => 'myRowsDeleted', old_key => 'rows_deleted',},
    {oid => 40, type => 'Counter32', new_key => 'myRowsRead', old_key => 'rows_read',},
    {oid => 41, type => 'Counter32', new_key => 'myTableLocksWaited', old_key => 'Table_locks_waited',},
    {oid => 42, type => 'Counter32', new_key => 'myTableLocksImmediate', old_key => 'Table_locks_immediate',},
    {oid => 43, type => 'Counter32', new_key => 'mySlowQueries', old_key => 'Slow_queries',},
    {oid => 44, type => 'Gauge32', new_key => 'myOpenFiles', old_key => 'Open_files',},
    {oid => 45, type => 'Gauge32', new_key => 'myOpenTables', old_key => 'Open_tables',},
    {oid => 46, type => 'Counter32', new_key => 'myOpenedTables', old_key => 'Opened_tables',},
    {oid => 47, type => 'Counter32', new_key => 'myInnodbOpenFiles', old_key => 'innodb_open_files',},
    {oid => 48, type => 'Counter32', new_key => 'myOpenFilesLimit', old_key => 'open_files_limit',},
    {oid => 49, type => 'Counter32', new_key => 'myTableCache', old_key => 'table_cache',},
    {oid => 50, type => 'Counter32', new_key => 'myAbortedClients', old_key => 'Aborted_clients',},
    {oid => 51, type => 'Counter32', new_key => 'myAbortedConnects', old_key => 'Aborted_connects',},
    {oid => 52, type => 'Counter32', new_key => 'myMaxUsedConnections', old_key => 'Max_used_connections',},
    {oid => 53, type => 'Counter32', new_key => 'mySlowLaunchThreads', old_key => 'Slow_launch_threads',},
    {oid => 54, type => 'Gauge32', new_key => 'myThreadsCached', old_key => 'Threads_cached',},
    {oid => 55, type => 'Gauge32', new_key => 'myThreadsConnected', old_key => 'Threads_connected',},
    {oid => 56, type => 'Counter32', new_key => 'myThreadsCreated', old_key => 'Threads_created',},
    {oid => 57, type => 'Gauge32', new_key => 'myThreadsRunning', old_key => 'Threads_running',},
    {oid => 58, type => 'Gauge32', new_key => 'myMaxConnections', old_key => 'max_connections',},
    {oid => 59, type => 'Gauge32', new_key => 'myThreadCacheSize', old_key => 'thread_cache_size',},
    {oid => 60, type => 'Counter32', new_key => 'myConnections', old_key => 'Connections',},
    {oid => 61, type => 'Gauge32', new_key => 'mySlaveRunning', old_key => 'slave_running',},
    {oid => 62, type => 'Gauge32', new_key => 'mySlaveStopped', old_key => 'slave_stopped',},
    {oid => 63, type => 'Counter32', new_key => 'mySlaveRetriedTransactions', old_key => 'Slave_retried_transactions',},
    {oid => 64, type => 'Gauge32', new_key => 'mySlaveLag', old_key => 'slave_lag',},
    {oid => 65, type => 'Gauge32', new_key => 'mySlaveOpenTempTables', old_key => 'Slave_open_temp_tables',},
    {oid => 66, type => 'Gauge32', new_key => 'myQcacheFreeBlocks', old_key => 'Qcache_free_blocks',},
    {oid => 67, type => 'Gauge32', new_key => 'myQcacheFreeMemory', old_key => 'Qcache_free_memory',},
    {oid => 68, type => 'Counter32', new_key => 'myQcacheHits', old_key => 'Qcache_hits',},
    {oid => 69, type => 'Counter32', new_key => 'myQcacheInserts', old_key => 'Qcache_inserts',},
    {oid => 70, type => 'Counter32', new_key => 'myQcacheLowmemPrunes', old_key => 'Qcache_lowmem_prunes',},
    {oid => 71, type => 'Counter32', new_key => 'myQcacheNotCached', old_key => 'Qcache_not_cached',},
    {oid => 72, type => 'Gauge32', new_key => 'myQcacheQueriesInCache', old_key => 'Qcache_queries_in_cache',},
    {oid => 73, type => 'Gauge32', new_key => 'myQcacheTotalBlocks', old_key => 'Qcache_total_blocks',},
    {oid => 74, type => 'Counter32', new_key => 'myQueryCacheSize', old_key => 'query_cache_size',},
    {oid => 75, type => 'Counter32', new_key => 'myQuestions', old_key => 'Questions',},
    {oid => 76, type => 'Counter32', new_key => 'myComUpdate', old_key => 'Com_update',},
    {oid => 77, type => 'Counter32', new_key => 'myComInsert', old_key => 'Com_insert',},
    {oid => 78, type => 'Counter32', new_key => 'myComSelect', old_key => 'Com_select',},
    {oid => 79, type => 'Counter32', new_key => 'myComDelete', old_key => 'Com_delete',},
    {oid => 80, type => 'Counter32', new_key => 'myComReplace', old_key => 'Com_replace',},
    {oid => 81, type => 'Counter32', new_key => 'myComLoad', old_key => 'Com_load',},
    {oid => 82, type => 'Counter32', new_key => 'myComUpdateMulti', old_key => 'Com_update_multi',},
    {oid => 83, type => 'Counter32', new_key => 'myComInsertSelect', old_key => 'Com_insert_select',},
    {oid => 84, type => 'Counter32', new_key => 'myComDeleteMulti', old_key => 'Com_delete_multi',},
    {oid => 85, type => 'Counter32', new_key => 'myComReplaceSelect', old_key => 'Com_replace_select',},
    {oid => 86, type => 'Counter32', new_key => 'mySelectFullJoin', old_key => 'Select_full_join',},
    {oid => 87, type => 'Counter32', new_key => 'mySelectFullRangeJoin', old_key => 'Select_full_range_join',},
    {oid => 88, type => 'Counter32', new_key => 'mySelectRange', old_key => 'Select_range',},
    {oid => 89, type => 'Counter32', new_key => 'mySelectRangeCheck', old_key => 'Select_range_check',},
    {oid => 90, type => 'Counter32', new_key => 'mySelectScan', old_key => 'Select_scan',},
    {oid => 91, type => 'Counter32', new_key => 'mySortMergePasses', old_key => 'Sort_merge_passes',},
    {oid => 92, type => 'Counter32', new_key => 'mySortRange', old_key => 'Sort_range',},
    {oid => 93, type => 'Counter32', new_key => 'mySortRows', old_key => 'Sort_rows',},
    {oid => 94, type => 'Counter32', new_key => 'mySortScan', old_key => 'Sort_scan',},
    {oid => 95, type => 'Counter32', new_key => 'myCreatedTmpTables', old_key => 'Created_tmp_tables',},
    {oid => 96, type => 'Counter32', new_key => 'myCreatedTmpDiskTables', old_key => 'Created_tmp_disk_tables',},
    {oid => 97, type => 'Counter32', new_key => 'myCreatedTmpFiles', old_key => 'Created_tmp_files',},
    {oid => 98, type => 'Counter64', new_key => 'myBytesSent', old_key => 'Bytes_sent',},
    {oid => 99, type => 'Counter64', new_key => 'myBytesReceived', old_key => 'Bytes_received',},
    {oid => 100, type => 'Gauge32', new_key => 'myInnodbLogBufferSize', old_key => 'innodb_log_buffer_size',},
    {oid => 101, type => 'Gauge32', new_key => 'myUnflushedLog', old_key => 'unflushed_log',},
    {oid => 102, type => 'Counter64', new_key => 'myLogBytesFlushed', old_key => 'log_bytes_flushed',},
    {oid => 103, type => 'Counter64', new_key => 'myLogBytesWritten', old_key => 'log_bytes_written',},
    {oid => 104, type => 'Counter32', new_key => 'myRelayLogSpace', old_key => 'relay_log_space',},
    {oid => 105, type => 'Gauge32', new_key => 'myBinlogCacheSize', old_key => 'binlog_cache_size',},
    {oid => 106, type => 'Counter32', new_key => 'myBinlogCacheDiskUse', old_key => 'Binlog_cache_disk_use',},
    {oid => 107, type => 'Counter32', new_key => 'myBinlogCacheUse', old_key => 'Binlog_cache_use',},
    {oid => 108, type => 'Counter32', new_key => 'myBinaryLogSpace', old_key => 'binary_log_space',},
    {oid => 109, type => 'Gauge32', new_key => 'myStateClosingTables', old_key => 'State_closing_tables',},
    {oid => 110, type => 'Gauge32', new_key => 'myStateCopyingToTmpTable', old_key => 'State_copying_to_tmp_table',},
    {oid => 111, type => 'Gauge32', new_key => 'myStateEnd', old_key => 'State_end',},
    {oid => 112, type => 'Gauge32', new_key => 'myStateFreeingItems', old_key => 'State_freeing_items',},
    {oid => 113, type => 'Gauge32', new_key => 'myStateInit', old_key => 'State_init',},
    {oid => 114, type => 'Gauge32', new_key => 'myStateLocked', old_key => 'State_locked',},
    {oid => 115, type => 'Gauge32', new_key => 'myStateLogin', old_key => 'State_login',},
    {oid => 116, type => 'Gauge32', new_key => 'myStatePreparing', old_key => 'State_preparing',},
    {oid => 117, type => 'Gauge32', new_key => 'myStateReadingFromNet', old_key => 'State_reading_from_net',},
    {oid => 118, type => 'Gauge32', new_key => 'myStateSendingData', old_key => 'State_sending_data',},
    {oid => 119, type => 'Gauge32', new_key => 'myStateSortingResult', old_key => 'State_sorting_result',},
    {oid => 120, type => 'Gauge32', new_key => 'myStateStatistics', old_key => 'State_statistics',},
    {oid => 121, type => 'Gauge32', new_key => 'myStateUpdating', old_key => 'State_updating',},
    {oid => 122, type => 'Gauge32', new_key => 'myStateWritingToNet', old_key => 'State_writing_to_net',},
    {oid => 123, type => 'Gauge32', new_key => 'myStateNone', old_key => 'State_none',},
    {oid => 124, type => 'Gauge32', new_key => 'myStateOther', old_key => 'State_other',},
    {oid => 125, type => 'Counter64', new_key => 'myAdditionalPoolAlloc', old_key => 'additional_pool_alloc',},
    {oid => 126, type => 'Counter64', new_key => 'myTotalMemAlloc', old_key => 'total_mem_alloc',},
    {oid => 127, type => 'Gauge32', new_key => 'myHashIndexCellsTotal', old_key => 'hash_index_cells_total',},
    {oid => 128, type => 'Gauge32', new_key => 'myHashIndexCellsUsed', old_key => 'hash_index_cells_used',},
    {oid => 129, type => 'Gauge32', new_key => 'myInnoDBLockStructs', old_key => 'innodb_lock_structs',},
    {oid => 130, type => 'Gauge32', new_key => 'myInnoDBLockWaitSecs', old_key => 'innodb_lock_wait_secs',},
    {oid => 131, type => 'Gauge32', new_key => 'myInnoDBTablesInUse', old_key => 'innodb_tables_in_use',},
    {oid => 132, type => 'Gauge32', new_key => 'myInnoDBLockedTables', old_key => 'innodb_locked_tables',},
    {oid => 133, type => 'Gauge32', new_key => 'myUncheckpointedBytes', old_key => 'uncheckpointed_bytes',},
    {oid => 134, type => 'Gauge32', new_key => 'myIBufCellCount', old_key => 'ibuf_cell_count',},
    {oid => 135, type => 'Gauge32', new_key => 'myIBufUsedCells', old_key => 'ibuf_used_cells',},
    {oid => 136, type => 'Gauge32', new_key => 'myIBufFreeCells', old_key => 'ibuf_free_cells',},
    {oid => 137, type => 'Counter64', new_key => 'myAdaptiveHashMemory', old_key => 'adaptive_hash_memory',},
    {oid => 138, type => 'Counter64', new_key => 'myPageHashMemory', old_key => 'page_hash_memory',},
    {oid => 139, type => 'Counter64', new_key => 'myDictionaryCacheMemory', old_key => 'dictionary_cache_memory',},
    {oid => 140, type => 'Counter64', new_key => 'myFileSystemMemory', old_key => 'file_system_memory',},
    {oid => 141, type => 'Counter64', new_key => 'myLockSystemMemory', old_key => 'lock_system_memory',},
    {oid => 142, type => 'Counter64', new_key => 'myRecoverySystemMemory', old_key => 'recovery_system_memory',},
    {oid => 143, type => 'Counter64', new_key => 'myThreadHashMemory', old_key => 'thread_hash_memory',},
    {oid => 144, type => 'Counter64', new_key => 'mySlaveLagBytes', old_key => 'slave_lag_bytes',},
    {oid => 145, type => 'Counter64', new_key => 'mySlaveLagBytesU', old_key => 'slave_lag_bytes_upper',},
    {oid => 146, type => 'Counter64', new_key => 'mySlaveLagBytesL', old_key => 'slave_lag_bytes_lower',},
    );

sub mysql_oidvals {
    return \@oidvals;
}

# This function has been translated from PHP to Perl from the
# excellent Baron Schwartz's MySQL Cacti Templates
sub fetch_mysql_data ($$$$;) {
    my ($datasource, $dbuser, $dbpass, $flags) = @_;
    my %output;
    # override default options with passed in settings
    map { $opt{$_} = $flags->{$_} if exists $opt{$_} } keys %$flags;

    eval {
        my $dbh = DBI->connect($datasource, $dbuser, $dbpass, {RaiseError => 1, AutoCommit => 1});
        if (!$dbh) {
            dolog(LOG_CRIT, "Can't connect to database: $datasource, $@");
            return;
        }

        my %status = (
            'transactions'         => 0,
            'relay_log_space'      => 0,
            'binary_log_space'     => 0,
            'slave_lag'            => 0,
            'slave_running'        => 0,
            'slave_stopped'        => 0,
            'State_closing_tables'       => 0,
            'State_copying_to_tmp_table' => 0,
            'State_end'                  => 0,
            'State_freeing_items'        => 0,
            'State_init'                 => 0,
            'State_locked'               => 0,
            'State_login'                => 0,
            'State_preparing'            => 0,
            'State_reading_from_net'     => 0,
            'State_sending_data'         => 0,
            'State_sorting_result'       => 0,
            'State_statistics'           => 0,
            'State_updating'             => 0,
            'State_writing_to_net'       => 0,
            'State_none'                 => 0,
            'State_other'                => 0,
        );

        my $result = $dbh->selectall_arrayref("SHOW /*!50002 GLOBAL */ STATUS");
        foreach my $row (@$result) {
            $status{$row->[0]} = $row->[1];
        }

        # Get SHOW VARIABLES and convert the name-value array into a simple
        # associative array.
        $result = $dbh->selectall_arrayref("SHOW /*!50002 GLOBAL */ VARIABLES");
        foreach my $row (@$result) {
            $status{$row->[0]} = $row->[1];
        }

        # Make table_open_cache backwards-compatible.
        if ( defined($status{'table_open_cache'}) ) {
           $status{'table_cache'} = $status{'table_open_cache'};
        }

        if ($opt{slave}) {
            $result = $dbh->selectrow_hashref('SHOW SLAVE STATUS');

	    for (keys %$result) {
		$result->{lc($_)} = $result->{$_};
	    }
	    $status{'relay_log_space'}  = $result->{'relay_log_space'};
	    $status{'slave_lag'}        = $result->{'seconds_behind_master'};

	    if ($opt{slavebytes}) {
		# simple case, the slave is currently handling the active
		# log file the master is still writing to
		if (defined($result->{'master_log_file'}) and $result->{'master_log_file'} ne '' and $result->{'master_log_file'} eq $result->{'relay_master_log_file'}) {
		    $status{'slave_lag_bytes'} = ($result->{'read_master_log_pos'} - $result->{'exec_master_log_pos'});
		} elsif (defined($result->{'master_host'}) and defined($result->{'master_port'})) {
		    my $mdsn;
		    if ($datasource =~ m/mysql_read_default_file/) {
			$mdsn = $datasource . ';host=' . $result->{'master_host'} . ';port=' . $result->{'master_port'};
		    } else {
			$mdsn = 'DBI:mysql:host='. $result->{'master_host'} . ';port=' . $result->{'master_port'};
		    }
		    # otherwise, try and connect to the master host with the
		    # same credentials to add up how far ahead the master is
		    my $mdbh = DBI->connect($mdsn, $dbuser, $dbpass, {PrintError => 0, PrintWarn => 0});
		    if (defined($mdbh)) {
			my $mbinlogs = $mdbh->selectall_arrayref('SHOW BINARY LOGS');
			my (%binlogs,$offset);
			foreach my $row (@$mbinlogs) {
			    $binlogs{$row->[0]} = $row->[1];
			}
			$offset = ($binlogs{$result->{'relay_master_log_file'}} - $result->{'exec_master_log_pos'});
			for my $binlog (sort keys %binlogs) {
			    # skip any log files <= slave's current read file
			    next unless (($binlog cmp $result->{'relay_master_log_file'}) == 1);
			    # add up the byte totals for anything newer
			    $offset += $binlogs{$binlog};
			}
			$status{'slave_lag_bytes'} = $offset;
		    }
		} else {
		    # could not determine slave_lag_bytes, set it to undef
		    $status{'slave_lag_bytes'} = undef;
		}
		if (defined($status{'slave_lag_bytes'})) {
		    # if perl is not using 64bit int values, the following will break,
		    # but it is unclear how to cleanly assign the value from the hash
		    # in that case, so stick to base operations for now
		    $status{'slave_lag_bytes_upper'} = $status{'slave_lag_bytes'} >> 32;
		    $status{'slave_lag_bytes_lower'} = $status{'slave_lag_bytes'} & 0xffffffff;
		} else {
		    $status{'slave_lag_bytes_upper'} = undef;
		    $status{'slave_lag_bytes_lower'} = undef;
		}
	    }

	    # Check replication heartbeat, if present.
	    if ( $opt{heartbeat} ne '' ) {
		my $row2 = $dbh->selectrow_arrayref("SELECT GREATEST(0, UNIX_TIMESTAMP() - UNIX_TIMESTAMP(ts) - 1) FROM $opt{heartbeat} WHERE id = 1");
		$status{'slave_lag'} = $row2->[0];
	    }

	    if (defined($result->{'slave_sql_running'}) and $result->{'slave_sql_running'} eq 'Yes') {
		$status{'slave_running'} = 1;
		$status{'slave_stopped'} = 0;
	    } else {
		$status{'slave_running'} = 0;
		$status{'slave_stopped'} = 1;
	    }

        }

        # Get info on master logs.
        my @binlogs = (0);
        if ($opt{master} && $status{'log_bin'} eq 'ON') {    # See issue #8
            $result = $dbh->selectall_arrayref(
                "SHOW MASTER LOGS",
                {Slice => {}}
            );
            foreach my $row (@$result) {
                my %newrow = map {lc($_) => $row->{$_}} keys %$row;

                # Older versions of MySQL may not have the File_size column in the
                # results of the command.
                if (exists($newrow{'file_size'})) {
                    push(@binlogs, $newrow{'file_size'});
                }
                else {
                    last;
                }
            }
        }

        # Get SHOW INNODB STATUS and extract the desired metrics from it.
        if ($opt{innodb} && $status{'have_innodb'} eq 'YES') {
            my $innodb_array = $dbh->selectall_arrayref("SHOW /*!50000 ENGINE*/ INNODB STATUS",{Slice => {}});
            my @lines = split("\n", $innodb_array->[0]{'Status'});

            my $out = $innodb_parser->parse_innodb_status(\@lines);

            # Override values from InnoDB parsing with values from SHOW STATUS,
            # because InnoDB status might not have everything and the SHOW STATUS is
            # to be preferred where possible.
            my %overrides = (
               'Innodb_buffer_pool_pages_data'  => 'database_pages',
               'Innodb_buffer_pool_pages_dirty' => 'modified_pages',
               'Innodb_buffer_pool_pages_free'  => 'free_pages',
               'Innodb_buffer_pool_pages_total' => 'pool_size',
               'Innodb_buffer_pool_reads'       => 'pages_read',
               'Innodb_data_fsyncs'             => 'file_fsyncs',
               'Innodb_data_pending_reads'      => 'pending_normal_aio_reads',
               'Innodb_data_pending_writes'     => 'pending_normal_aio_writes',
               'Innodb_os_log_pending_fsyncs'   => 'pending_log_flushes',
               'Innodb_pages_created'           => 'pages_created',
               'Innodb_pages_read'              => 'pages_read',
               'Innodb_pages_written'           => 'pages_written',
               'Innodb_rows_deleted'            => 'rows_deleted',
               'Innodb_rows_inserted'           => 'rows_inserted',
               'Innodb_rows_read'               => 'rows_read',
               'Innodb_rows_updated'            => 'rows_updated',
            );

            # If the SHOW STATUS value exists, override...
            foreach my $key (keys %overrides) {
               if ( defined($status{$key}) ) {
                  $out->{$overrides{$key}} = $status{$key};
               }
            }

            foreach my $key (keys %$out) {
                $status{$key} = $out->{$key};
            }

	    if (defined($status{'innodb_log_buffer_size'})) {
		if (defined($status{'unflushed_log'})) {
		    $status{'unflushed_log'} = $status{'innodb_log_buffer_size'} if
		      $status{'innodb_log_buffer_size'} gt $status{'unflushed_log'};
		} else {
		    $status{'unflushed_log'} = $status{'innodb_log_buffer_size'};
		}
	    }
        }


        # Get SHOW PROCESSLIST and aggregate it by state, then add it to the array
        # too.
        if ( $opt{procs} ) {
            $result = $dbh->selectall_arrayref("SHOW PROCESSLIST",{Slice => {}});
            foreach my $row (@$result) {
                my %newrow = map {lc($_) => $row->{$_}} keys %$row;
                my $state = $newrow{'state'};
                unless ( defined($state) ) {
                    $state = 'NULL';
                }
                if ( $state eq '' ) {
                    $state = 'none';
                }
                $state = lc($state);
                $state =~ s/ /_/;
                if ( defined($status{"State_$state"}) ) {
                    $status{"State_$state"} += 1;
                }
                else {
                    $status{"State_other"} += 1;
                }
            }
        }

        if (scalar @binlogs) {
            $status{'binary_log_space'} = 0;
            foreach my $log (@binlogs) {
                $status{'binary_log_space'} += $log;
            }
        }

        $dbh->disconnect();

	# store available status values in the output hash translating
	# from 'old' to 'new' key names along the way
	foreach my $oid (@oidvals) {
	    if (exists($status{$oid->{'old_key'}})) {
		$output{$oid->{'new_key'}} = {value => $status{$oid->{'old_key'}},
					      type => $oid->{'type'},
					     };
	    } else {
		# mark any unavailable values as undef so they will be skipped
		$output{$oid->{'new_key'}} = undef;
	    }
	}
    };
    if ($@) {
        dolog(LOG_CRIT, "can't refresh data from mysql: $@\n");
	return;
    }
    return \%output;
}

sub dolog {
    my ($level, $msg) = @_;
    syslog($level, $msg);
}

1;

__END__
